import pymysql
import time
import hashlib


class MYSQL(object):
    def __init__(self):

        try:
            self.mysql = pymysql.connect(host='huawei.wuangwang.top', port=3306, user='root', password='a123456789.',
                                         db='sentry')
            self.cursor = self.mysql.cursor()
        except Exception as e:
            print(e)
            print('链接数据库失败')

    def closeMYSql(self):
        self.mysql.close()

    def insertDevice(self, MAC, location):
        device_ID = "select max(device_ID) from my_device"
        try:
            self.cursor.execute(device_ID)
            device_ID = self.cursor.fetchall()
            sql = "INSERT INTO my_device (MAC,location,device_ID) VALUES ('{0}',{1},{2})" \
                .format(MAC, location, int(device_ID[0][0]) + 1)
            self.cursor.execute(sql)
            self.mysql.commit()
            return
        except Exception as e:
            self.mysql.rollback()
            return e

    def selestIDbyName(self, StudentID):
        sql = "SELECT `name` FROM my_student WHERE `student_ID` = '{0}'".format(StudentID)
        try:
            self.cursor.execute(sql)
            select_result = self.cursor.fetchall()
            if select_result:
                return select_result[0][0]
            else:
                return 'No such person found!'
        except Exception as e:
            self.mysql.rollback()
            return e

    def selestMACbyAll(self, MAC):
        sql = "SELECT `ID`,`MAC`,`location`  FROM my_device WHERE `MAC` = '{0}'".format(MAC)
        try:
            self.cursor.execute(sql)
            select_result = self.cursor.fetchall()
            return select_result
        except Exception as e:
            self.mysql.rollback()
            return e

    def insertCheckIn(self, student_id, location):
        INSERT = "INSERT INTO my_check_in (student,check_in_time,device_ID) VALUES ({0},now(),{1})".format(student_id,
                                                                                                           location)
        SELECT = "SELECT (NOW() - MAX(check_in_time)) > 1000 FROM my_check_in WHERE student = {0}".format(student_id,
                                                                                                          location)
        try:
            self.cursor.execute(SELECT)
            select_result = self.cursor.fetchall()[0][0]
            # print(type(select_result))
            # select_result = 1
            if select_result == 1 or select_result is None:
                self.cursor.execute(INSERT)
                self.mysql.commit()
                return "record complete"
            else:
                return "record in within 10 minutes"
        except Exception as e:
            self.mysql.rollback()
            return e

    # def Log_in(self, ADD):
    #     record_time = str(int(time.time()))
    #     sql = "SELECT * FROM MAC WHERE UID = '{0}'".format(ADD)
    #     self.cursor.execute(sql)
    #     select_result = self.cursor.fetchall()
    #     if select_result:
    #         data = hashlib.sha1((select_result + record_time).encode('utf-8')).hexdigest()
    #     else:
    #         data = 'The device is not registered'
    #     return data


mq = MYSQL()
# qwe = mq.insertDevice('22', '123')
# qwe = mq.selestMACbyAll('e0:db:55:b2:79:f1')
qwe = mq.insertCheckIn(10001, 22)
# qwe = mq.selestIDbyName("10001")
print(qwe)
mq.closeMYSql()
